﻿using System;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MySql.Data;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace GestionMedic
{
    class Requetes
    {
        private static MySqlDataAdapter da;
        private static DataSet ds;
        private static DataTable dt;
        private static MySqlCommand cmd;

        // Connexion a la base de données
        private static string con = "server=172.17.100.40;user=userGsb;database=gsb_frais;port=3306;password=secret;";
        private static MySqlConnection cnx = new MySqlConnection(con);

        // Ajouter un médicament
        public static void ajouter(String nom, String compo, String depot, String effet, String ci, int prix, int famille)
        {
            cnx.Close();
            cnx.Open();

            // Definition de la requète
            String req;
            req = "Insert into medicament (nom, compo, depot, effets, contreIndic, prix, idFamille)"
                    + "Values ('" + nom + "', '" + compo + "', '" + depot + "', '" + effet + "', '" + ci + "', " + prix + ", "+famille+")";

            // Execution de la requète
            MySqlCommand cmd = cnx.CreateCommand();
            cmd.CommandText = req;
            cmd.ExecuteNonQuery();

            cnx.Close();
        }

        // Modifier un médicament existant
        public static void modifier(String nom, String compo, String depot, String effet, String ci, int prix)
        {
            cnx.Open();
            // Definition de la requète
            String req = "Update medicament "
                        +"Set compo = '"+compo+"', depot = '"+depot+"', effets = '"+effet+"', contreIndic = '"+ci+"', prix = "+prix+" Where nom = '"+nom+"'";

            // Execution de la requète
            MySqlCommand cmd = cnx.CreateCommand();
            cmd.CommandText = req;
            cmd.ExecuteNonQuery();
            cnx.Close();
        }

        // Supprimer un médicament existant
        public static void supprimer(string nom)
        {
            cnx.Open();
            // Definition de la requète
            String req = "Delete from medicament "
                        +"Where nom = '"+nom+"'";

            // Execution de la requète
            MySqlCommand cmd = cnx.CreateCommand();
            cmd.CommandText = req;
            cmd.ExecuteNonQuery();
            cnx.Close();
        }

        // Retourne l'id de la famille du medicament
        public static int chercherId(String famille)
        {
            cnx.Close();
            cnx.Open();
            int id = 0;
            String req = "Select id From famille Where nom = '"+famille+"'";
            cmd = new MySqlCommand(req, cnx);
            try
            {
                id = (int)cmd.ExecuteScalar();
                cnx.Close();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }                        

            return id;
        }
        
        // Alimente l'onglet Supprimer
        public static String champs(String colonne, String donnee)
        {
            cnx.Close();
            cnx.Open();
            cmd = new MySqlCommand("Select "+colonne+" From medicament Where  nom = '"+donnee+"';", cnx);
            string valeur;
            try
            {
                if (colonne == "prix") 
                {
                    Int32 entier = (Int32)cmd.ExecuteScalar();
                    valeur = entier.ToString();
                }
                else if (colonne == "depot")
                {
                    DateTime date = (DateTime)cmd.ExecuteScalar();
                    valeur = date.ToString();

                }
                else 
                {
                    valeur = (string)cmd.ExecuteScalar();
                }
            }
            catch(Exception e)
            {
                MessageBox.Show(e.ToString());
                valeur = null;
            }
            cnx.Close();
            return valeur;
        }

        // Alimenter les combobox
        public static DataTable alimenterCB(String table)
        {
            cnx.Close();
            cnx.Open();
            da = new MySqlDataAdapter("Select nom from "+table+" Order By id;", cnx);
            dt = new DataTable();
            da.Fill(dt);
            cnx.Close();

            return dt;
        }
    }
}
